﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess.IService;
using System.Data;
using Utility;
using System.Collections;
using System.Data.SqlClient;
namespace DataAccess.Service
{
    public class SearchService : ISearchService
    {
        public DataSet getTuitionDataSet(String acaValue, String majValue, String graValue, String classValue, String yearValue)
        {
            String commandText = "select * " +
            "from Student,StudentPaymentProject,PaymentProject " +
            "where StudentPaymentProject.projectId = PaymentProject.projectId " +
                "and StudentPaymentProject.stuId = Student.stuId " +
                "and academy like @acaValue " +
                "and firstMajor like @majValue " +
                "and grade like @graValue " +
                "and classNum like @classValue " +
                "and year like @yearValue";
            SqlParameter[] paras = new SqlParameter[]{
                 new SqlParameter("@acaValue","%" + acaValue + "%"),
                 new SqlParameter("@majValue","%" + majValue + "%"),
                 new SqlParameter("@graValue","%" + graValue + "%"),
                 new SqlParameter("@classValue","%" + classValue + "%"),
                 new SqlParameter("@yearValue","%" + yearValue + "%")
            };
            return SQLHelper.ExecuteDataSet(commandText,CommandType.Text,paras);
        }


        public DataSet getTuitionDetail(String acaValue, String majValue, String graValue, String classValue, String yearValue)
        {
            String commandText = "select * " +
                                "from Student,StudentPaymentProject,StudentPaymentProjectDetail,PaymentProject,PaymentProjectDetail " +
                                "where StudentPaymentProject.projectId = PaymentProject.projectId " +
                                    "and StudentPaymentProjectDetail.stuProjectId = StudentPaymentProject.projectId " +
                                    "and PaymentProjectDetail.projectId = PaymentProject.projectId " +
                                    "and StudentPaymentProject.stuId = Student.stuId " +
                                    "and StudentPaymentProjectDetail.detailId = PaymentProjectDetail.detailId " +
                                    "and academy like '%%' " +
                                    "and firstMajor like '%%' " +
                                    "and grade like '%%' " +
                                    "and classNum like '%%' " +
                                    "and year like '%%'";
            SqlParameter[] paras = new SqlParameter[]{
                 new SqlParameter("@acaValue","%" + acaValue + "%"),
                 new SqlParameter("@majValue","%" + majValue + "%"),
                 new SqlParameter("@graValue","%" + graValue + "%"),
                 new SqlParameter("@classValue","%" + classValue + "%"),
                 new SqlParameter("@yearValue","%" + yearValue + "%")
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }




        public DataSet getUtility(String utilityYearAndMonth, String apartmentValue, String dorNumValue, int switchYM)
        {
            
            switch (switchYM)
            {
                case 0:     //年月都没选择
                    utilityYearAndMonth = "%%";
                    break;
                case 1:     //年没选中月选中
                    utilityYearAndMonth = "____" + utilityYearAndMonth;
                    break;
                case 2:     //年选中月没选中
                    utilityYearAndMonth = utilityYearAndMonth + "__";
                    break;
                case 3:        //都选中
                    //即本身（占6个字节）
                    break;
                default:
                    break;
            }


            /*String commandText = "select * " +
                                "from Student,StudentAccommodation,Dormitory,UtilityPayment,UtilitiesHistoryOrder,Apartment " +
                                "where Student.stuId = StudentAccommodation.stuId " +
                                    "and StudentAccommodation.dorId = Dormitory.dorId " +
                                    "and Dormitory.dorId = UtilityPayment.dorId " +
                                    "and UtilityPayment.utilityId = UtilitiesHistoryOrder.utilityId " +
                                    "and Apartment.apaId = Dormitory.apaId " +
                                    "and date like @utilityYearAndMonth " +
                                    "and Apartment.apaId like @apartmentValue " +
                                    "and dorNum like @dorNumValue";*/

            String commandText = "select * " +
                                "from Dormitory,UtilityPayment,Apartment  " +
                                "where  Dormitory.dorId = UtilityPayment.dorId  " +
                                    "and Apartment.apaId = Dormitory.apaId  " +
                                    "and date like '%' " +
                                    "and Apartment.apaId like '%' " +
                                    "and dorNum like '%'";

            SqlParameter[] paras = new SqlParameter[]{
                 new SqlParameter("@utilityYearAndMonth",utilityYearAndMonth),
                 new SqlParameter("@apartmentValue","%" + apartmentValue + "%"),
                 new SqlParameter("@dorNumValue","%" + dorNumValue + "%")
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }







    }
}
